package utility;

import java.sql.Connection;
import servlets.ValidateUser;
import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import dao.DatabaseConnectionPool;

public class UserSession {
	
	private static Connection conn;
	private static Statement stmt;
	private static String userID;
	public UserSession()
	{
	}
	
	static{
		conn = DatabaseConnectionPool.getConnection();
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public String addUser(String emailID,String password,String question, String answer)
	{
		String result = null;
		int num = 100+(int)(Math.random()*99);
		String userID = "user"+num;
		String query = "Insert into QSS.UserInfo(`UserID`, `EmailID`, `Password`, `SecurityQuestion`, `SQAnswer`) Values('"+userID+"','"+emailID+"','"+password+"','"+question+"','"+answer+"')";
		try {
			if(stmt.executeUpdate(query)==1)
				result = "Success";
		} catch (SQLException e) {
			//e.printStackTrace();
			result = "DB error. Please try again.";
		}
		
		return result;
	}
	public static boolean setFeedback(String userID, String responses[])
	{
		boolean result = false;
		try {
			int num = 100+(int)(Math.random()*999);
			String FeedbackID = "Feedback"+num;
			String query = "Insert into Feedback(FeedbackID, UserID, OverallScore, GamePlayScore, MarketPlaceScore, AccountInfoScore, DisplayScore) Values('"+FeedbackID+"','"+userID+"','"+responses[0]+"','"+responses[1]+"','"+responses[2]
				+"','"+responses[3]+"','"+responses[4]+"')";
			result = stmt.execute(query);
		
		if(stmt.getUpdateCount()>0)
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static ResultSet getQuestion(String Topic,String SubTopic,String ExactTopic)
	{
		ResultSet result = null;
		String query = "Select * from QSS.Question where";
System.out.println(Topic);
		if(Topic!=null)
			query=query+" (Topic = '"+Topic+"')";

		if(SubTopic!=null&&SubTopic.length()>0)
			query+=" AND (SubTopic = '"+SubTopic+"')";

		if(ExactTopic!=null&&ExactTopic.length()>0)
			query+=" AND (SpecialTopic = '"+ExactTopic+"')";
		System.out.println(query);
		try {
			result = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static boolean validateUser(String userName, String password)
	{
		boolean validity = false;
		String query = "Select Password,UserID from QSS.UserInfo where EmailID = '"+userName+"'";
		//System.out.println(query);
		try {
			ResultSet result  = stmt.executeQuery(query);
			if(result.next())
			{
				String pwd = result.getString("Password");
				//System.out.println(password);
				if(pwd.equals(password))
				{
					validity = true;
					userID = result.getString("UserID");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return validity;
	}
	
	public static String getUserID()
	{
		return userID;
	}
	
	public static ResultSet getScoreInfo(String userID)
	{
		ResultSet result = null;
		String query = "Select * from QSS.ScoreBoard where UserID = '"+userID+"'";
		try {
			result = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static boolean updateScoreBoard(String userID,float overallscore,int numCorrect, int numAttempted, int totalQuizNum, int recentScore)
	{
		boolean updateResult = false;
		if(numAttempted>10)
		{
		String query = "Update QSS.ScoreBoard Set UserID ='"+userID+"',OverallPerformance ="+overallscore+",NumCorrectQues="+numCorrect+
				",NumAttemptedQues="+numAttempted+",TotalQuizNum="+totalQuizNum+",RecentScore="+recentScore;
		try {
			int updateCount = stmt.executeUpdate(query);
			if(updateCount>0)
				updateResult = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		else
		{
			String query = "Insert into QSS.ScoreBoard Set UserID ='"+userID+"',OverallPerformance ="+overallscore+",NumCorrectQues="+numCorrect+
					",NumAttemptedQues="+numAttempted+",TotalQuizNum="+totalQuizNum+",RecentScore="+recentScore;
			try {
				int updateCount = stmt.executeUpdate(query);
				if(updateCount>0)
					updateResult = true;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return updateResult;
	}
	
	public static ResultSet getQuizesOwned(String userID)
	{
		ResultSet result =null;
		String query = "Select * from QuizzesOwned where UserID = '"+userID+"'";
		try {
			result = stmt.executeQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	public String addCard(String cardno, String name, String cvv,
			String expiration) {
		String result = null;
		String query = "Insert into QSS.creditinfo(`cardno`, `name`, `cvv`, `expidt`) Values('"+cardno+"','"+name+"','"+cvv+"','"+expiration+"')";
		try {
			if(stmt.executeUpdate(query)==1)
				result = "Success";
		} catch (SQLException e) {
			//e.printStackTrace();
			result = "DB error. Please try again.";
		}
		
		return result;
}
	public String addPurchasedQuiz(String topic, String s) {
		String result = null;
		String userid=userID;
		String query = "Insert into QSS.quizzesowned(`UserID`, `Topic`, `SubTopic`) Values('"+userid+"','"+topic+"','"+s+"')";
		try {
			if(stmt.executeUpdate(query)==1)
				result = "Success";
		} catch (SQLException e) {
			//e.printStackTrace();
			result = "DB error. Please try again.";
		}
		
		return result;
	}
	public String addQuestion(String question, String optionA, String optionB,
			String optionC, String optionD, String option) {
		String result = null;
		String query = "Insert into QSS.questionssubmitted(`Question`, `OptionA`, `OptionB`, `OptionC`, `OptionD`, `Answer`) Values('"+question+"','"+optionA+"','"+optionB+"','"+optionC+"','"+optionD+"','"+option+"')";
		
		String query1="Update Qss.userinfo SET `RewardPoints`=`RewardPoints`+10 where `UserID`='"+userID+"'";
		try {
			if(stmt.executeUpdate(query)==1){
				result = "Success";
				stmt.executeUpdate(query1);
			}
			} catch (SQLException e) {
			//e.printStackTrace();
			result = "DB error. Please try again.";
		}
		return result;
	}
	public static int getRewardpoints() throws SQLException {
		// TODO Auto-generated method stub
		String q="select `RewardPoints` from QSS.userinfo where `UserID`='"+userID+"'";
		PreparedStatement res = conn.prepareStatement(q);
	    ResultSet r1=res.executeQuery();
	    int rewards = 0;
	    while (r1.next()) {
	    	rewards=r1.getInt("RewardPoints");
	    }
		return rewards;
	}
	public String changePassword(String oldPassword, String newPassword) throws SQLException {
		String result = null;
		String query = "select `Password` from QSS.userinfo where `UserID`='"+userID+"'";
		PreparedStatement res = conn.prepareStatement(query);
	    ResultSet r1=res.executeQuery();
	    String pwd = null;
	    while (r1.next()) {
	    	pwd=r1.getString("Password");
			}

	    String query2 = null;
		if(pwd.equals(oldPassword))
		{
			query2 = "Update QSS.userinfo SET `Password`='"+newPassword+"' where `UserID`='"+userID+"'";
		}
		try {
			if(stmt.executeUpdate(query2)==1){
				result = "Success";
			}
			} catch (SQLException e) {
			//e.printStackTrace();
			result = "Please enter you correct old password!";
		}
		return result;
	}

}
